package com.data;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author sergiu.marin
 * @since 05.03.2014
 */
public class DaoUtils {

    private static final Logger log = Logger.getLogger(DaoUtils.class);

    @Autowired
    private ApplicationConfiguration applicationConfiguration;
    private static DaoUtils daoUtils;
    private Connection connection = null;

    private DaoUtils() {
        this.connection = DBUtils.getConnection(
                        applicationConfiguration.getProperty("change.tracker.db.driver.name"),
                        applicationConfiguration.getProperty("change.tracker.db.url"),
                        Integer.valueOf(applicationConfiguration.getProperty("change.tracker.db.port")),
                        applicationConfiguration.getProperty("change.tracker.db.name"),
                        applicationConfiguration.getProperty("change.tracker.db.username"),
                        applicationConfiguration.getProperty("change.tracker.db.password"));
    }


    public void listTableRows() {
        try {

            String query = "SELECT * FROM db_tables";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet rs = ps.executeQuery();

            System.out.println("db_tables table rows data:");
            int i = 1;
            while (rs.next()) {
                System.out.printf("-row:[%d] id:[%d], table_name:[%s], read_time:[%s], pk_column:[%s], active:[%b]",
                        i, rs.getInt("id"), rs.getString("table_name"), rs.getDate("read_time"), rs.getString("pk_column"), rs.getBoolean("active"));
                System.out.println();
                i++;
            }

        } catch (SQLException e) {
            log.error("Error reading data from data_table");
        } finally {
            if (this.connection != null) {
                try {
                    this.connection.close();
                } catch (SQLException e) {
                    log.error("Error to close connection.");
                }
            }
        }
    }

    public synchronized static DaoUtils newInstance() {
        if (daoUtils == null) {
            daoUtils = new DaoUtils();
        }
        return daoUtils;
    }

    public void addNewScanTable(String tableName, String primaryKey) {
        String insert = "INSERT INTO db_tables(table_name, primary_key_column) VALUES (?, ?)";

        try {
            PreparedStatement ps = connection.prepareStatement(insert);
            ps.setString(1, tableName);
            ps.setString(2, primaryKey);
            ps.executeUpdate();
        } catch (SQLException e) {
            log.error("Error insert to update db_tables table.");
        } finally {
            if (this.connection != null) {
                try {
                    this.connection.close();
                } catch (SQLException e) {
                    log.error("Error to close connection.");
                }
            }
        }
    }

    public void activateScanRow(boolean activate, String tableName) {
        try {

            String update = "UPDATE db_tables set active=? WHERE table_name=?";
            PreparedStatement ps = connection.prepareStatement(update);
            ps.setBoolean(1, activate);
            ps.setString(2, tableName);
            ps.executeUpdate();

        } catch (SQLException e) {
            log.error("Error activate/deactivate read from " + tableName + " table.");
        } finally {
            if (this.connection != null) {
                try {
                    this.connection.close();
                } catch (SQLException e) {
                    log.error("Error to close connection.");
                }
            }
        }
    }

}
